﻿using System;
using System.Data;
using System.Text;
using System.Data.SQLite;
using System.Collections.ObjectModel;

namespace Clipper.DAL
{
    /// <summary>
    /// 数据访问类:ReagentKit
    /// </summary>
    public partial class ReagentKit
    {
        public ReagentKit()
        { }
        #region  BasicMethod

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Clipper.Model.ReagentKit model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ReagentKit(");
            strSql.Append("holeNumber,loadDate1,loadDate2,ReagentID1,ReagentID2)");
            strSql.Append(" values (");
            strSql.Append("@holeNumber,@loadDate1,@loadDate2,@ReagentID1,@ReagentID2)");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@holeNumber", DbType.Int16,1),
					new SQLiteParameter("@loadDate1", DbType.DateTime),
					new SQLiteParameter("@loadDate2", DbType.Int32,4),
					new SQLiteParameter("@ReagentID1", DbType.Int32,4),
					new SQLiteParameter("@ReagentID2", DbType.String,20)};
            parameters[0].Value = model.holeNumber;
            parameters[1].Value = model.loadDate1;
            parameters[2].Value = model.loadDate2;
            parameters[3].Value = model.ReagentID1;
            parameters[4].Value = model.ReagentID2;


            int rows = SQLiteHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Clipper.Model.ReagentKit model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ReagentKit set ");
            strSql.Append("loadDate1=@loadDate1,");
            strSql.Append("loadDate2=@loadDate2,");
            strSql.Append("ReagentID1=@ReagentID1,");
            strSql.Append("ReagentID2=@ReagentID2,");
            strSql.Append("reagentName=@reagentName");
            strSql.Append(" where holeNumber=@holeNumber ");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@loadDate1", DbType.DateTime),
					new SQLiteParameter("@loadDate2", DbType.Int32,4),
					new SQLiteParameter("@ReagentID1", DbType.Int32,4),
					new SQLiteParameter("@ReagentID2", DbType.String,20),
                    new SQLiteParameter("@reagentName", DbType.String,20),
                    new SQLiteParameter("@holeNumber", DbType.Int16,1)};
                    
            parameters[0].Value = model.loadDate1;
            parameters[1].Value = model.loadDate2;
            parameters[2].Value = model.ReagentID1;
            parameters[3].Value = model.ReagentID2;
            parameters[4].Value = model.ReagentName;
            parameters[5].Value = model.holeNumber;
            int rows = SQLiteHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateReagentQuantity(Clipper.Model.ReagentKit model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ReagentKit set ");
            //strSql.Append("loadDate1=@loadDate1,");
            strSql.Append("loadDate2=@loadDate2 ");
            //strSql.Append("ReagentID1=@ReagentID1,");
            //strSql.Append("ReagentID2=@ReagentID2,");
            //strSql.Append("reagentName=@reagentName");
            strSql.Append(" where holeNumber=@holeNumber ");
            SQLiteParameter[] parameters = {
                    //new SQLiteParameter("@loadDate1", DbType.DateTime),
                    new SQLiteParameter("@loadDate2", DbType.Int32,4),
                    //new SQLiteParameter("@ReagentID1", DbType.Int32,4),
                    //new SQLiteParameter("@ReagentID2", DbType.String,20),
                    //new SQLiteParameter("@reagentName", DbType.String,20),
                    new SQLiteParameter("@holeNumber", DbType.Int16,1)};

            //parameters[0].Value = model.loadDate1;
            parameters[0].Value = model.loadDate2;
            //parameters[2].Value = model.ReagentID1;
            //parameters[3].Value = model.ReagentID2;
            //parameters[4].Value = model.ReagentName;
            parameters[1].Value = model.holeNumber;
            int rows = SQLiteHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int holeNumber)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ReagentKit ");
            strSql.Append(" where holeNumber=@holeNumber ");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@holeNumber", DbType.Int16,1)			};
            parameters[0].Value = holeNumber;

            int rows = SQLiteHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Clipper.Model.ReagentKit GetModel(int holeNumber)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select holeNumber,loadDate1,loadDate2,ReagentID1,ReagentID2,reagentName from ReagentKit ");
            strSql.Append(" where holeNumber=@holeNumber ");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@holeNumber", DbType.Int16,1)			};
            parameters[0].Value = holeNumber;

            Clipper.Model.ReagentKit model = new Clipper.Model.ReagentKit();
            DataSet ds = SQLiteHelper.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Clipper.Model.ReagentKit DataRowToModel(DataRow row)
        {
            Clipper.Model.ReagentKit model = new Clipper.Model.ReagentKit();
            if (row != null)
            {
                if (row["holeNumber"] != null && row["holeNumber"].ToString() != "")
                {
                    model.holeNumber = int.Parse(row["holeNumber"].ToString());
                }
                if (row["loadDate1"] != null && row["loadDate1"].ToString() != "")
                {
                    model.loadDate1 = DateTime.Parse(row["loadDate1"].ToString());
                }
                if (row["loadDate2"] != null && row["loadDate2"].ToString() != "")
                {
                    model.loadDate2 = int.Parse(row["loadDate2"].ToString());
                }
                if (row["ReagentID1"] != null && row["ReagentID1"].ToString() != "")
                {
                    model.ReagentID1 = int.Parse(row["ReagentID1"].ToString());
                }
                if (row["ReagentID2"] != null && row["ReagentID2"].ToString() != "")
                {
                    model.ReagentID2 = (row["ReagentID2"].ToString());
                }
                if (row["reagentName"] != null && row["reagentName"].ToString() != "")
                {
                    model.ReagentName = row["reagentName"].ToString();
                }

            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select holeNumber,loadDate1,loadDate2,ReagentID1,reagentName,ReagentID2 ");
            strSql.Append(" FROM ReagentKit ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SQLiteHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM ReagentKit ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = SQLiteHelper.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.reagentID desc");
            }
            strSql.Append(")AS Row, T.*  from ReagentKit T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return SQLiteHelper.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@tblName", DbType.VarChar, 255),
                    new SQLiteParameter("@fldName", DbType.VarChar, 255),
                    new SQLiteParameter("@PageSize", DbType.Int32),
                    new SQLiteParameter("@PageIndex", DbType.Int32),
                    new SQLiteParameter("@IsReCount", DbType.bit),
                    new SQLiteParameter("@OrderType", DbType.bit),
                    new SQLiteParameter("@strWhere", DbType.VarChar,1000),
                    };
            parameters[0].Value = "ReagentKit";
            parameters[1].Value = "reagentID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return SQLiteHelper.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/
        #region //ArmMoveControl
        //获取所有的元素
        public ObservableCollection<Clipper.Model.ReagentKit> GetModelListReagentKit()
        {
            DataSet ds = GetList("");
            return DataTableToList(ds.Tables[0]);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public ObservableCollection<Clipper.Model.ReagentKit> DataTableToList(DataTable dt)
        {
            ObservableCollection<Clipper.Model.ReagentKit> modelList = new ObservableCollection<Clipper.Model.ReagentKit>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                Clipper.Model.ReagentKit model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = DataRowToModel(dt.Rows[n]);
                    if (model != null)
                    {
                        //填充试剂1、2实体
                        if (model.ReagentID1 != null)
                        {
                            Model.Reagent reagent1 = null;
                            reagent1 = GetItemsReagent(model.ReagentID1.Value);
                            if (reagent1 != null)
                            {
                                model.ReagentModel1 = reagent1;
                            }
                        }
                        //if (model.ReagentID2 != null)
                        //{
                        //    Model.Reagent reagent2 = null;
                        //    reagent2 = mReagentBll.GetItemsReagent(model.ReagentID2.Value);
                        //    if (reagent2 != null)
                        //    {
                        //        model.ReagentModel2 = reagent2;
                        //    }
                        //}
                        modelList.Add(model);
                    }
                }
            }
            return modelList;
        }
        public Clipper.Model.Reagent GetItemsReagent(int reagentID)
        {
            Model.Reagent model = GetReagentModel(reagentID);
            if (model != null)
            {
                model.DetectItemModel = GetDetectItemModel(model.DetectItem);
            }
            return model;
        }
        public Clipper.Model.Reagent GetReagentModel(int reagentID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select reagentID,brand,DetectItem,batchNumber,peirodValidity,barcode,reagentOrder,ReagentLeft,StateId from Reagent ");
            strSql.Append(" where reagentID=@reagentID");
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@reagentID", DbType.Int32,4)
            };
            parameters[0].Value = reagentID;

            Clipper.Model.Reagent model = new Clipper.Model.Reagent();
            DataSet ds = SQLiteHelper.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModelReagent(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        public Clipper.Model.DetectItems GetDetectItemModel(string detectItem)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select detectItem,name,DetectVarietyID,bloodAmount,reagentName1,reagentAmount1,reagentName2,reagentAmount2,reagentName3,reagentAmount3,incubationTime,incubationTime1,incubationTime2,detectTime,standardAmount,FittingApproachID,correlationThreshold,CoordinateID,standardState,StandardInfoID,note from DetectItems ");
            strSql.Append(" where detectItem=@detectItem ");
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@detectItem", DbType.String,20)            };
            parameters[0].Value = detectItem;

            Clipper.Model.DetectItems model = new Clipper.Model.DetectItems();
            DataSet ds = SQLiteHelper.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModelDetectItem(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        public Clipper.Model.DetectItems DataRowToModelDetectItem(DataRow row)
        {
            Clipper.Model.DetectItems model = new Clipper.Model.DetectItems();
            if (row != null)
            {
                if (row["detectItem"] != null)
                {
                    model.detectItem = row["detectItem"].ToString();
                }
                if (row["name"] != null)
                {
                    model.name = row["name"].ToString();
                }
                if (row["DetectVarietyID"] != null && row["DetectVarietyID"].ToString() != "")
                {
                    model.DetectVarietyID = int.Parse(row["DetectVarietyID"].ToString());
                }
                if (row["bloodAmount"] != null && row["bloodAmount"].ToString() != "")
                {
                    model.bloodAmount = int.Parse(row["bloodAmount"].ToString());
                }
                //设置名字1
                if (row["reagentName1"] != null)
                {
                    model.reagentName1 = row["reagentName1"].ToString();
                }
                //设置名字2
                if (row["reagentName2"] != null)
                {
                    model.reagentName2 = row["reagentName2"].ToString();
                }
                //设置名字3
                if (row["reagentName3"] != null)
                {
                    model.reagentName3 = row["reagentName3"].ToString();
                }
                if (row["reagentAmount1"] != null && row["reagentAmount1"].ToString() != "")
                {
                    model.reagentAmount1 = byte.Parse(row["reagentAmount1"].ToString());
                }
                if (row["reagentAmount2"] != null && row["reagentAmount2"].ToString() != "")
                {
                    model.reagentAmount2 = byte.Parse(row["reagentAmount2"].ToString());
                }
                if (row["reagentAmount3"] != null && row["reagentAmount3"].ToString() != "")
                {
                    model.reagentAmount3 = byte.Parse(row["reagentAmount3"].ToString());
                }
                if (row["incubationTime"] != null && row["incubationTime"].ToString() != "")
                {
                    model.incubationTime = byte.Parse(row["incubationTime"].ToString());
                }
                if (row["incubationTime1"] != null && row["incubationTime1"].ToString() != "")
                {
                    model.incubationTime1 = byte.Parse(row["incubationTime1"].ToString());
                }
                if (row["incubationTime2"] != null && row["incubationTime2"].ToString() != "")
                {
                    model.incubationTime2 = byte.Parse(row["incubationTime2"].ToString());
                }
                if (row["detectTime"] != null && row["detectTime"].ToString() != "")
                {
                    model.detectTime = int.Parse(row["detectTime"].ToString());
                }
                if (row["standardAmount"] != null && row["standardAmount"].ToString() != "")
                {
                    model.standardAmount = int.Parse(row["standardAmount"].ToString());
                }
                if (row["FittingApproachID"] != null && row["FittingApproachID"].ToString() != "")
                {
                    model.FittingApproachID = int.Parse(row["FittingApproachID"].ToString());
                }
                if (row["correlationThreshold"] != null && row["correlationThreshold"].ToString() != "")
                {
                    model.correlationThreshold = decimal.Parse(row["correlationThreshold"].ToString());
                }
                if (row["CoordinateID"] != null && row["CoordinateID"].ToString() != "")
                {
                    model.CoordinateID = int.Parse(row["CoordinateID"].ToString());
                }
                if (row["standardState"] != null && row["standardState"].ToString() != "")
                {
                    model.standardState = byte.Parse(row["standardState"].ToString());
                }
                if (row["StandardInfoID"] != null && row["StandardInfoID"].ToString() != "")
                {
                    model.StandardInfoID = int.Parse(row["StandardInfoID"].ToString());
                }
                if (row["note"] != null)
                {
                    model.note = row["note"].ToString();
                }
            }
            return model;
        }

        public Clipper.Model.Reagent DataRowToModelReagent(DataRow row)
        {
            Clipper.Model.Reagent model = new Clipper.Model.Reagent();
            if (row != null)
            {
                if (row["reagentID"] != null && row["reagentID"].ToString() != "")
                {
                    model.reagentID = int.Parse(row["reagentID"].ToString());
                }
                if (row["brand"] != null)
                {
                    model.brand = row["brand"].ToString();
                }
                if (row["DetectItem"] != null)
                {
                    model.DetectItem = row["DetectItem"].ToString();
                }
                if (row["batchNumber"] != null)
                {
                    model.batchNumber = row["batchNumber"].ToString();
                }
                if (row["peirodValidity"] != null && row["peirodValidity"].ToString() != "")
                {
                    model.peirodValidity = DateTime.Parse(row["peirodValidity"].ToString());
                }
                if (row["barcode"] != null)
                {
                    model.barcode = row["barcode"].ToString();
                }
                if (row["reagentOrder"] != null && row["reagentOrder"].ToString() != "")
                {
                    model.reagentOrder = int.Parse(row["reagentOrder"].ToString());
                }
                if (row["ReagentLeft"] != null && row["ReagentLeft"].ToString() != "")
                {
                    model.ReagentLeft = int.Parse(row["ReagentLeft"].ToString());
                }
                if (row["StateId"] != null && row["StateId"].ToString() != "")
                {
                    model.StateId = int.Parse(row["StateId"].ToString());
                    //model.StateModel = mReagentDal.GetModel(model.StateId);
                }
            }
            return model;
        }


        #endregion




        #endregion  BasicMethod
        #region  ExtensionMethod

        #region // 获得孔位编号
        /// <summary>
        /// 获得孔位编号
        /// </summary>
        /// <param name="reagentID">试剂ID</param>
        /// <param name="reagOrder">试剂1，还是试剂2</param>
        /// <returns>孔位编号</returns>
        public byte GetHoleNumber(int reagentID, byte reagOrder)
        {
            byte holeNo = 0;
            StringBuilder strSql = new StringBuilder();

            // 如果是试剂1
            if (reagOrder == 1)
            {
                strSql.Append("select holeNumber from ReagentKit where ReagentID1 = " + reagentID.ToString());
            }
            else if (reagOrder == 2)// 如果是试剂2
            {
                strSql.Append("select holeNumber from ReagentKit where ReagentID2 = " + reagentID.ToString());
            }

            object ob = SQLiteHelper.GetSingle(strSql.ToString());

            if (ob != null)
            {
                holeNo = Convert.ToByte(ob);
            }
            return holeNo;
        }
        #endregion // 获得孔位编号


        #region // 获得孔位编号
        /// <summary>
        /// 获得孔位编号
        /// </summary>
        /// <param name="reagentID">试剂ID</param>
        /// <param name="reagOrder">试剂1，还是试剂2</param>
        /// <returns>孔位编号</returns>
        public byte GetHoleNumber1(int reagentID)
        {
            byte holeNo = 0;
            StringBuilder strSql = new StringBuilder();


                strSql.Append("select holeNumber from ReagentKit where ReagentID1 = " + reagentID.ToString());



            object ob = SQLiteHelper.GetSingle(strSql.ToString());

            if (ob != null)
            {
                holeNo = Convert.ToByte(ob);
            }
            return holeNo;
        }
        #endregion // 获得孔位编号
        #endregion  ExtensionMethod
    }
}

